﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F4AssemblyPrint : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private string plan;
        public panel_F4AssemblyPrint()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.plan = null;
            LoadPlanList();            
        }

        //////////////////////////////////////////////////
        /// Load plan list
        //////////////////////////////////////////////////
        private void LoadPlanList()
        {
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT PlanNo FROM [231_TimePlan] WHERE (FinishedDate IS NULL) GROUP BY PlanNo";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Value = dataReader["PlanNo"].ToString().Trim();
                    item.Text = dataReader["PlanNo"].ToString().Trim();
                    this.planNo.Properties.Items.Add(item);
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get plan no list.");
            }
        }

        //////////////////////////////////////////////////
        /// Load data from database to grid
        //////////////////////////////////////////////////
        private void ShowData()
        {
            Unit.Columns.Clear();
            gridUnit.DataSource = null;
            Robot.Columns.Clear();
            gridRobot.DataSource = null;
            if (this.planNo.SelectedIndex >= 0)
            {
                ComboboxItem item = (ComboboxItem)this.planNo.SelectedItem;
                this.plan = item.Value.ToString();

                DataSet unitDataSet = new DataSet();
                SQLManagement mySQLManagement = new SQLManagement();
                mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
                string sql = "SELECT PlanNo, TimePlanNo, CompID, Quantity, CustomerID, FromSerialNo, ToSerialNo, DeadLine"
                    + " FROM [231_TimePlan] "
                    + " WHERE (FromSerialNo LIKE N'HB%' OR FromSerialNo LIKE N'UB%')"
                    + " AND (PlanNo LIKE N'" + this.plan + "') ORDER BY TimePlanNo";
                unitDataSet = mySQLManagement.SQL_Query(sql);
                if (unitDataSet != null)
                {
                    gridUnit.DataSource = unitDataSet.Tables[0];
                }
                else
                {
                    MessageBox.Show("There are no data for plan: " + this.plan);
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("PlanNo", typeof(string));
                    newTable.Columns.Add("TimePlanNo", typeof(string));
                    newTable.Columns.Add("CompID", typeof(string));
                    newTable.Columns.Add("Quantity", typeof(float));
                    newTable.Columns.Add("Customer", typeof(string));
                    newTable.Columns.Add("FromSerialNo", typeof(int));
                    newTable.Columns.Add("ToSerialNo", typeof(string));
                    newTable.Columns.Add("DeadLine", typeof(DateTime));
                    gridUnit.DataSource = newTable;
                }
                Unit.Columns[0].Caption = "Plan No";
                Unit.Columns[1].Caption = "Order No";
                Unit.Columns[2].Caption = "TComp No";
                Unit.Columns[3].Caption = "Quantity";
                Unit.Columns[4].Caption = "Customer";
                Unit.Columns[5].Caption = "From Serial No";
                Unit.Columns[6].Caption = "To Serial No";
                Unit.Columns[7].Caption = "Export Date";
                Unit.BestFitColumns();

                string orderNo = null, compID = null, customerID = null;
                int qty = 0;
                string exportDate = null; ;
                DataTable SRDT = new DataTable();
                SRDT.Columns.Add("PlanNo", typeof(string));
                SRDT.Columns.Add("OrderNo", typeof(string));
                SRDT.Columns.Add("CompID", typeof(string));
                SRDT.Columns.Add("SerialNo", typeof(string));                

                DataTable RBDT = new DataTable();
                RBDT.Columns.Add("STT", typeof(int));
                RBDT.Columns.Add("Robot Comp", typeof(string));
                RBDT.Columns.Add("Robot S/N", typeof(string));
                RBDT.Columns.Add("Plan No", typeof(string));
                RBDT.Columns.Add("Order No", typeof(string));
                RBDT.Columns.Add("Customer", typeof(string));
                RBDT.Columns.Add("Export Date", typeof(string));
                try
                {
                    myConnection.Open();                    
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT PlanNo, TimePlanNo, CompID, Quantity, CustomerID, FromSerialNo, DeadLine"
                        + " FROM [231_TimePlan] WHERE (FromSerialNo LIKE N'RB%') AND (PlanNo LIKE N'" + this.plan + "')";
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        orderNo = dataReader["TimePlanNo"].ToString().Trim();
                        compID = dataReader["CompID"].ToString().Trim();
                        customerID = dataReader["CustomerID"].ToString().Trim();
                        qty = Int32.Parse(dataReader["Quantity"].ToString().Trim());
                        exportDate = dataReader["DeadLine"].ToString().Trim();
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();


                    if (!string.IsNullOrEmpty(compID))
                    {
                        for (int i = 0; i < qty; i++)
                        {
                            RBDT.Rows.Add();
                            RBDT.Rows[i]["STT"] = i + 1;
                            RBDT.Rows[i]["Robot Comp"] = compID;
                            RBDT.Rows[i]["Plan No"] = this.plan;
                            RBDT.Rows[i]["Order No"] = orderNo;
                            RBDT.Rows[i]["Customer"] = customerID;
                            RBDT.Rows[i]["Export Date"] = exportDate;
                        }
                        sql_cmd = myConnection.CreateCommand();
                        sql_cmd.CommandText = "SELECT PlanNo, OrderNo, CompID, SerialNo"
                            + " FROM [231_RobotOrderDetail] WHERE (PlanNo LIKE N'" + this.plan + "')"
                            + " AND (SerialNo LIKE N'HB%' OR SerialNo LIKE N'UB%' OR SerialNo LIKE N'RB%')"
                            + " ORDER BY OrderNo, SerialNo";
                        dataReader = sql_cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            DataRow new_Row = SRDT.NewRow();
                            new_Row["PlanNo"] = dataReader["PlanNo"].ToString().Trim();
                            new_Row["OrderNo"] = dataReader["OrderNo"].ToString().Trim();
                            new_Row["CompID"] = dataReader["CompID"].ToString().Trim();
                            new_Row["SerialNo"] = dataReader["SerialNo"].ToString().Trim();
                            SRDT.Rows.Add(new_Row);
                        }
                        dataReader.Close();
                        sql_cmd.Dispose();
                                               

                        if (SRDT.Rows.Count > 0)
                        {
                            int RI = 0, RII = 0;
                            string CMP = null;

                            foreach (DataRow SRDT_Row in SRDT.Rows)
                            {
                                CMP = SRDT_Row["CompID"].ToString().Trim();
                                if (CMP != compID)
                                {
                                    if (!RBDT.Columns.Contains(CMP))
                                    {
                                        RI = 0;
                                        RBDT.Columns.Add(CMP, Type.GetType("System.String"));
                                    }
                                    for (int i = 0; i < qty; i++)
                                    {
                                        if (string.IsNullOrEmpty(RBDT.Rows[i][CMP].ToString()))
                                        {
                                            RI = i;
                                            break;
                                        }
                                    }
                                    RBDT.Rows[RI][CMP] = SRDT_Row["SerialNo"].ToString();                                    
                                }
                                else
                                {
                                    RBDT.Rows[RII]["Robot S/N"] = SRDT_Row["SerialNo"].ToString();                                    
                                    RII += 1;
                                }
                            }
                        }
                    }

                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                }

                if (RBDT.Rows.Count > 0)
                {
                    gridRobot.DataSource = RBDT;
                }
                else
                {
                    MessageBox.Show("There is no data for robot.");
                }
                Robot.BestFitColumns();
            }
        }

        private void planNo_SelectedIndexChanged(object sender, EventArgs e)
        {
            ShowData();
        }

        private void buttonView_Click(object sender, EventArgs e)
        {
            ShowData();
        }
    }
}
